package columbia.edu;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import oracle.jdbc.pool.OracleDataSource;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
/**
* Servlet implementation class OracleServlet
*/
public class RecordOrderDetails extends HttpServlet {
private static final long serialVersionUID = 1L;
private static final String connect_string =
//"jdbc:oracle:thin:scott/tiger@//w4111b.cs.columbia.edu:1521/ADB";
	"jdbc:oracle:thin:ab3416/kFxJssQk@//w4111b.cs.columbia.edu:1521/ADB";
private Connection conn;
/**
* @see HttpServlet#HttpServlet()
*/
public RecordOrderDetails() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
PrintWriter pw = new PrintWriter(response.getOutputStream());
try {
if (conn == null) {
// Create a OracleDataSource instance and set URL
OracleDataSource ods = new OracleDataSource();
ods.setURL(connect_string);

conn = (Connection) request.getSession().getAttribute("Connection");
if (conn == null) {
	conn = ods.getConnection();
	request.getSession().setAttribute("Connection", conn);
}
}
Statement stmt = conn.createStatement();
//ResultSet rset = stmt.executeQuery("select ENAME from EMP");
String custID = (String)(request.getSession().getAttribute("theName"));
custID=custID.trim();

//TODO: Calculate Total Price from Quantity entered
double total_price = 0;

String pid = (request.getSession().getAttribute("ID")).toString();
ResultSet rset = stmt.executeQuery("select price from products where pid = '" + pid + "'");

rset.next();

double price = rset.getDouble("price");
int qty = (Integer) request.getSession().getAttribute("qty");
total_price = price * qty;

Calendar calendar = Calendar.getInstance();
SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy");
String currentDate = "";

try {
    
	currentDate= dateFormat.format(calendar.getTime());
    
} catch (Exception e) {
    e.printStackTrace();
}

//String insertStatement2 = "insert into orders(cust_id, orderdate, total_price) values('" + custID + "','" + currentDate + "'," + total_price + ")";
stmt.executeUpdate("insert into orders(cust_id, orderdate, total_price) values('" + custID.trim() + "','" + currentDate + "'," + total_price + ")");
//stmt.executeUpdate("insert into orders(cust_id, orderdate, total_price) values('39daisy','04-APR-2011',0)");
response.setContentType("text/html");


rset = stmt.executeQuery("select max(orderno) as orderid from orders where cust_id = '" + custID.trim() + "'");
response.setContentType("text/html");

rset.next();
int orderid = rset.getInt("orderid".trim());

//TODO: Set qty to quantity entered by user

//int qty=1;


String insertStatement = "insert into sales values(" + orderid + ",'" + pid + "'," + qty;
//stmt.executeUpdate("insert into sales values(" + orderid + ",'" + pid + "'," + qty);
stmt.executeUpdate("insert into sales values(" + orderid + ",'" + pid.trim() + "'," + qty + ")");
response.setContentType("text/html");



pw.println("<html>");
pw.println("<head><title>Confirmation of order</title></head>");
pw.println("<link rel='stylesheet' href='style.css' type='text/css' charset='utf-8' />");
pw.println("<BR><BR>");
pw.println("<H1>Your order has been placed! <BR></H1>");
pw.println("<BR><BR>");
pw.println("<H2>Your order no is " + orderid +  "<BR></H2>");
//pw.println("<H1>" + insertStatement + "<BR></H1>");
//pw.println("<H1>" + insertStatement2 + "<BR></H1>");
//pw.println("ID " + ID);
//pw.println("<body><BR>");

//rset = stmt.executeQuery("select )

pw.println("<div id='nav-top'>");
pw.println("<ul>");
pw.println("<li><a href='Logout.jsp'>Logout</a> |</li>");
//pw.println("<li><a href='Account.jsp'>My Account</a> |</li>");
pw.println("<li><a href='Contact.jsp'>Contact us</a></li>");
pw.println("</ul>");
pw.println("</div>");

pw.println("</html>");

} catch (SQLException e) {
pw.println(e.getMessage());
}
pw.close();

	/*
	try {
		
		if (conn != null) {
		conn.close();
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} */
}

/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
}
}